<?php
require_once('config.php');

$con = mysql_connect($dbhost,$dbuser,$dbpassword);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db($dbname, $con);

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment;filename=caidfighters.xml");

$sql = 'SELECT p.id , p.society_name, p.membership_expiration_date as expdate, '
		. ' l.name as location, '
        . ' count(distinct f.id) as authcount, count(distinct w.id) as warrantcount'
        . ' FROM people as p '
        . ' left join fighter_authorizations as f on p.id=f.person_id and f.suspended=0 '
        . ' left join marshal_warrants as w on p.id=w.person_id AND w.suspended=0'
        . ' left join locations as l on p.location_id = l.id'
        . ' WHERE p.society_name <> "" '
        . ' group by 1, 2 order by 2';
        
$result = mysql_query($sql);      
 print "<?xml version=\"1.0\"?>\n";
 print "<?mso-application progid=\"Excel.Sheet\"?>\n";  
?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>caidmarshal</Author>
  <LastAuthor>caidmarshal</LastAuthor>
  <LastPrinted>2008-03-19T22:00:54Z</LastPrinted>
  <Created>2008-03-13T21:52:58Z</Created>
  <LastSaved>2008-03-19T22:01:04Z</LastSaved>
  <Company>ATC Las Vegas</Company>
  <Version>11.9999</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8445</WindowHeight>
  <WindowWidth>14955</WindowWidth>
  <WindowTopX>240</WindowTopX>
  <WindowTopY>375</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s23">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="8"/>
   <Interior/>
  </Style>
  <Style ss:ID="s24">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="8"/>
   <Interior/>
  </Style>
  <Style ss:ID="s25">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
   <Interior/>
   <NumberFormat/>
  </Style>
  <Style ss:ID="s26">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
  </Style>
  <Style ss:ID="s27">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
   <Interior/>
   <NumberFormat ss:Format="mm/dd/yy;@"/>
  </Style>
  <Style ss:ID="s28">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
   <NumberFormat ss:Format="mm/dd/yy;@"/>
  </Style>
  <Style ss:ID="s29">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
   <Interior/>
   <NumberFormat ss:Format="mm/dd/yy;@"/>
  </Style>
  <Style ss:ID="s30">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
   <Interior/>
   <NumberFormat ss:Format="m/d/yy;@"/>
  </Style>
  <Style ss:ID="s31">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
   <Interior/>
  </Style>
  <Style ss:ID="s32">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
   <Interior/>
  </Style>
  <Style ss:ID="s33">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
   <Interior/>
   <NumberFormat ss:Format="Short Date"/>
  </Style>
  <Style ss:ID="s34">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
   <Interior/>
   <NumberFormat ss:Format="Short Date"/>
  </Style>
  <Style ss:ID="s35">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
   <Interior/>
   <NumberFormat ss:Format="mmm\-yy"/>
  </Style>
  <Style ss:ID="s36">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:Size="7"/>
   <Interior/>
   <NumberFormat ss:Format="m/d/yy;@"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Names>
   <NamedRange ss:Name="Print_Titles" ss:RefersTo="=Sheet1!C1,Sheet1!R1"/>
  </Names>
  <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="<?php echo( mysql_num_rows($result) + 1 ); ?>" x:FullColumns="1"
   x:FullRows="1" ss:StyleID="s23" ss:DefaultRowHeight="11.25">
   <Column ss:StyleID="s23" ss:Width="165"/>
   <Column ss:StyleID="s23" ss:Width="115"/>
   <Column ss:StyleID="s23" ss:Width="34.5"/>
   <Column ss:StyleID="s23" ss:Width="650" />
   <Column ss:StyleID="s23" ss:Width="300"/>
   <Row ss:Height="18.75">
    <Cell ss:StyleID="s32"><Data ss:Type="String">Society Name</Data><NamedCell
      ss:Name="Print_Titles"/></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">Society Location</Data><NamedCell
      ss:Name="Print_Titles"/></Cell>
    <Cell ss:StyleID="s27"><Data ss:Type="String">Card Exp.</Data><NamedCell
      ss:Name="Print_Titles"/></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">Authorizations</Data><NamedCell
      ss:Name="Print_Titles"/></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">Warrants & Offices</Data><NamedCell
      ss:Name="Print_Titles"/></Cell>
   </Row>
   <?php
   while($row = mysql_fetch_array($result)) {
   	 print "<Row ss:Height=\"11.25\">\n";
   	 print '<Cell ss:StyleID="s25">';
   	 if (!empty($row["society_name"])) {
   	   print '<Data ss:Type="String">'.$row["society_name"].'</Data>';
   	 }
   	 print '<NamedCell ss:Name="Print_Titles"/></Cell>';
   	 print '<Cell ss:StyleID="s25">';
   	 if (!empty($row["location"])) {
   	   print '<Data ss:Type="String">'.$row["location"].'</Data>';
   	 }
     print '</Cell><Cell ss:StyleID="s27">';
     if (!empty($row["expdate"])) {
   	   print '<Data ss:Type="DateTime">'.$row["expdate"].'</Data>';
   	 } 
   	 print "</Cell><Cell>";
   	 $sql = 'select f.abbr, a.auth_date from fighter_authorizations as a, martial_forms as f '
		  . 'where a.martial_form_id = f.id and a.suspended = 0 and a.person_id = '.$row["id"];
	 $authresult = mysql_query($sql);
	 if (mysql_num_rows($authresult) > 0) {
	 	print '<Data ss:Type="String">';
	 }
	 while($authrow = mysql_fetch_array($authresult)) {
	   print $authrow["abbr"].':'.$authrow["auth_date"].' ';
	 }
   	 if (mysql_num_rows($authresult) > 0) {
	 	print '</Data>';
	 }
     print "</Cell><Cell>";
	 $sql = 'SELECT t.abbr, a.auth_date FROM marshal_warrants AS a '
	 	  . 'LEFT JOIN marshal_types AS t ON a.marshal_type_id = t.id '
	 	  . 'where a.suspended = 0 and a.person_id = '.$row["id"];
	 $warrantresult = mysql_query($sql);
	 if (mysql_num_rows($warrantresult) > 0) {
	 	print '<Data ss:Type="String">';
	 }
	 while($warrantrow = mysql_fetch_array($warrantresult)) {
	   print $warrantrow["abbr"].':'.$warrantrow["auth_date"].' ';
	 }
   	 if (mysql_num_rows($warrantresult) > 0) {
	 	print '</Data>';
	 }
   	 print "</Cell>";
   	 print "</Row>\n";
   }?>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Layout x:Orientation="Landscape"/>
   </PageSetup>
   <Unsynced/>
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <FreezePanes/>
   <FrozenNoSplit/>
   <SplitHorizontal>1</SplitHorizontal>
   <TopRowBottomPane>1</TopRowBottomPane>
   <ActivePane>2</ActivePane>
   <Panes>
    <Pane>
     <Number>3</Number>
    </Pane>
    <Pane>
     <Number>2</Number>
     <ActiveRow>1</ActiveRow>
     <RangeSelection>R1</RangeSelection>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
  <Sorting xmlns="urn:schemas-microsoft-com:office:excel">
   <Sort>Society Name</Sort>
  </Sorting>
 </Worksheet>
</Workbook>
   